package edu.luas.digitalmedia.dao;

import edu.luas.digitalmedia.pojo.MyAddress;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class MyAddressDao {
    /*
     * @description: 添加收货地址
     * @author: sjw
     * @date:
     * @param: 添加的地址实体对象
     * @return: 添加影响的行数
     **/
    public int insert(MyAddress model) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1:从连接池中获取 connection 对象
            connection = DBHelper.getConnection();

            //2:创建执行语句对象 statement
            String sql = "insert into sec_c6_areamanage (name, phone , area , area1 , types) values(?,?,?,?,?) ";
            statement = connection.prepareStatement(sql);
            statement.setObject(1, model.getName());
            statement.setObject(2, model.getPhone());
            statement.setObject(3, model.getArea());
            statement.setObject(4, model.getArea1());
            statement.setObject(5, model.getTypes());


            //3：执行 sql 语句，返回影响行数
            int count = statement.executeUpdate();

            //4:处理结果
            return count;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            //5：关闭资源
            DBHelper.close(statement, connection);
        }
    }

    public List<MyAddress> selectList() throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<MyAddress> models = new ArrayList<>();
        try {
            //1:从连接池中获取 connection 对象
            connection = DBHelper.getConnection();
            //2:创建执行语句对象 statement
            String sql = "select * from sec_c6_areamanage";
            statement = connection.prepareStatement(sql);
            //3：执行 sql 语句，返回影响结果集
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                MyAddress model = new MyAddress();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setPhone(resultSet.getString("phone"));
                model.setArea(resultSet.getString("area"));
                model.setArea1(resultSet.getString("area1"));
                model.setTypes(resultSet.getString("types"));
                models.add(model);
            }
            //4:处理结果
            return models;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            //5：关闭资源
            DBHelper.close(resultSet, statement, connection);
        }
    }

    public MyAddress selectOne(Integer id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //1:从连接池中获取 connection 对象
            connection = DBHelper.getConnection();

            //2:创建执行语句对象 statement
            String sql = "select * from sec_c6_areamanage where id=?";
            statement = connection.prepareStatement(sql);
            statement.setObject(1, id);

            //3：执行 sql 语句，返回影响行数
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                MyAddress model = new MyAddress();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setPhone(resultSet.getString("phone"));
                model.setArea(resultSet.getString("area"));
                model.setArea1(resultSet.getString("area1"));
                model.setTypes(resultSet.getString("types"));
                return model;
            }

            //4:处理结果
            return null;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            //5：关闭资源
            DBHelper.close(resultSet, statement, connection);
        }
    }

    public int update(MyAddress model) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1:从连接池中获取 connection 对象
            connection = DBHelper.getConnection();

            //2:创建执行语句对象 statement
            String sql = "update sec_c6_areamanage set `name`=?, phone=?, area=?, area1=?, types=? where id=?";
            statement = connection.prepareStatement(sql);
            statement.setObject(1, model.getName());
            statement.setObject(2, model.getPhone());
            statement.setObject(3, model.getArea());
            statement.setObject(4, model.getArea1());
            statement.setObject(5, model.getTypes());
            statement.setObject(6, model.getId());

            //3：执行 sql 语句，返回影响行数
            int count = statement.executeUpdate();

            //4:处理结果
            return count;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        } finally {
            //5：关闭资源
            DBHelper.close(statement, connection);
        }
    }

    public int delete(Integer id) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1:从连接池中获取 connection 对象
            connection = DBHelper.getConnection();

            //2:创建执行语句对象 statement
            String sql = "delete from sec_c6_areamanage where id=?";
            statement = connection.prepareStatement(sql);
            statement.setObject(1, id);

            //3：执行 sql 语句，返回影响行数
            int count = statement.executeUpdate();

            //4:处理结果
            return count;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        } finally {
            //5：关闭资源
            DBHelper.close(statement, connection);
        }
    }
}
